package com.iaiai.browser.activity.database;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.iaiai.browser.activity.entiry.Favorites;
import com.iaiai.browser.activity.entiry.History;
import com.iaiai.browser.core.util.DateUtil;

/**
 * 
 * <p>
 * Title: DatabaseOpenHelper.java
 * </p>
 * <p>
 * E-Mail: 176291935@qq.com
 * </p>
 * <p>
 * QQ: 176291935
 * </p>
 * <p>
 * Http: iaiai.iteye.com
 * </p>
 * <p>
 * Create time: 2011-12-9
 * </p>
 * 
 * @author 丸子
 * @version 0.0.1
 */
public class DatabaseOpenHelper extends SQLiteOpenHelper {

	private static final String DBNAME = "iaiai"; // 数据库名子

	private static final int VERSION = 1; // 版本

	public DatabaseOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	public DatabaseOpenHelper(Context context) {
		super(context, DBNAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE FAVORITES(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME VARCHAR(200), URL TEXT, CREATE_DATE VARCHAR(20),FILENAME VARCHAR(200))");
		db.execSQL("CREATE TABLE HISTORY(ID INTEGER PRIMARY KEY AUTOINCREMENT, URL TEXT, CREATE_DATE VARCHAR(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// 升级更新
		db.execSQL("DROP TABLE IF EXISTS FAVORITES");
		db.execSQL("DROP TABLE IF EXISTS HISTORY");
		onCreate(db);
	}
	
	public List<Favorites> queryFavorites(){
		List<Favorites> list = new ArrayList<Favorites>();
		Cursor cursor = getWritableDatabase().rawQuery(
				"select ID,NAME,URL,CREATE_DATE,FILENAME from FAVORITES order by ID desc", null);
		while (cursor.moveToNext()) {
			Favorites favorites = new Favorites();
			favorites.setId(cursor.getInt(0));
			favorites.setName(cursor.getString(1));
			favorites.setUrl(cursor.getString(2));
			favorites.setCreateDate(cursor.getString(3));
			favorites.setFilename(cursor.getString(4));
			list.add(favorites);
		}
		cursor.close();
		getWritableDatabase().close();
		return list;
	}
	
	/**
	 * 是否收藏了此标题
	 * @param title 标题
	 * @return
	 */
	public boolean queryFavoritesByTitle(String title){
		List<Favorites> list = new ArrayList<Favorites>();
		Cursor cursor = getWritableDatabase().rawQuery(
				"select ID,NAME,URL,CREATE_DATE from FAVORITES where NAME=? order by ID desc", new String[]{title});
		if(cursor.getCount()>0){
			cursor.close();
			getWritableDatabase().close();
			
			return true;
		}else{
			cursor.close();
			getWritableDatabase().close();
			
			return false;
		}
	}
	
	/**
	 * 收藏
	 * @param id
	 * @return
	 */
	public Favorites queryFavoritesById(Integer id){
		Favorites favorites = new Favorites();
		Cursor cursor = getWritableDatabase().rawQuery(
				"select ID,NAME,URL,CREATE_DATE,FILENAME from FAVORITES where ID="+id+" order by ID desc", null);
		while (cursor.moveToNext()) {
			favorites.setId(cursor.getInt(0));
			favorites.setName(cursor.getString(1));
			favorites.setUrl(cursor.getString(2));
			favorites.setCreateDate(cursor.getString(3));
			favorites.setFilename(cursor.getString(4));
		}
		cursor.close();
		getWritableDatabase().close();
		return favorites;
	}
	
	/**
	 * 是否收藏了此url
	 * @param url 地址
	 * @return
	 */
	public boolean queryFavoritesByUrl(String url){
		List<Favorites> list = new ArrayList<Favorites>();
		Cursor cursor = getWritableDatabase().rawQuery(
				"select ID,NAME,URL,CREATE_DATE from FAVORITES where URL=? order by ID desc", new String[]{url});
		if(cursor.getCount()>0){
			cursor.close();
			getWritableDatabase().close();
			
			return true;
		}else{
			cursor.close();
			getWritableDatabase().close();
			
			return false;
		}
	}
	
	/**
	 * 添加收藏
	 * @param title 标题
	 * @param url 地址
	 */
	public void insertFavorites(String title,String url,String filename){
		if(filename==null){
			getWritableDatabase().execSQL(
					"insert into FAVORITES(NAME,URL,CREATE_DATE) values(?,?,?)",
					new Object[] { title,url, DateUtil.getDateTime() });
		}else{
			getWritableDatabase().execSQL(
					"insert into FAVORITES(NAME,URL,CREATE_DATE,FILENAME) values(?,?,?,?)",
					new Object[] { title,url, DateUtil.getDateTime(),filename });
		}
		getWritableDatabase().close();
	}

	/**
	 * 添加历史记录
	 * 
	 * @param url
	 */
	public void insertHistory(String url) {
		getWritableDatabase().execSQL(
				"insert into HISTORY(URL,CREATE_DATE) values(?,?)",
				new Object[] { url, DateUtil.getDateTime() });
		getWritableDatabase().close();
	}
	
	/**
	 * 查询历史记录
	 * @param start
	 * @param end
	 * @return
	 */
	public List<History> queryHistory(int start, int end) {
		List<History> list = new ArrayList<History>();
		Cursor cursor = getWritableDatabase().rawQuery(
				"select ID,URL,CREATE_DATE from HISTORY order by ID desc limit "
						+ start + "," + end, null);
		while (cursor.moveToNext()) {
			History history = new History();
			history.setId(cursor.getInt(0));
			history.setUrl(cursor.getString(1));
			history.setDate(cursor.getString(2));
			list.add(history);
		}
		cursor.close();
		getWritableDatabase().close();
		return list;
	}
	
	/**
	 * 清空历史记录
	 */
	public void clearHistory(){
		getWritableDatabase().execSQL("delete from HISTORY");
		getWritableDatabase().close();
	}
	
	/**
	 * 删除
	 * @param id
	 */
	public void deleteFavorites(Integer id){
		getWritableDatabase().execSQL("delete from FAVORITES where ID=?",new Object[]{id});
		getWritableDatabase().close();
	}
}
